﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;
using System.Threading;
using System.Data.OleDb;
using System.Threading.Tasks;
using wfaFoxMigrator.Extensions;
using Oracle.DataAccess.Client;
using System.Data.SqlClient;

namespace wfaFoxMigrator
{
    public partial class frmMainFM : Form
    {
        #region Delegates
        delegate void SetTextCallback(string text);
        delegate void AddControlCallback(Control ctrl, int col, int row);
        delegate void ProgressCallback(ProgressBar pgb);
        delegate void SetMaximumToPb(ProgressBar pgb, int maximum);
        
        #endregion

        #region Variables And Properties
        bool AskAgain;
        bool ReWriteData;
        AutoCompleteStringCollection AutoComp = new AutoCompleteStringCollection();
        DirectoryInfo dippal;
        public bool StopAllProcess { get; set; }
        CancellationTokenSource cts = default(CancellationTokenSource);
        List<ListItem> lstItemsChecked = new List<ListItem>();
        List<ListItem> lstItemsBase = new List<ListItem>();
        SortedList<string, DataTable> lstSchemas = new SortedList<string, DataTable>();
        SortedList<string, DataTable> lstData = new SortedList<string, DataTable>();
        public string CurrentDBCS { get { return CommonFunctions.GetCurrentCS(); } }
        private frmCurrentlyProcessing _frmCp;
        public frmCurrentlyProcessing frmCp
        {
            get
            {
                if (_frmCp == null)
                {
                    _frmCp = new frmCurrentlyProcessing();
                    _frmCp.OnPressCancel += new frmCurrentlyProcessing.PressCancel(_frmCp_OnPressCancel);
                }
                return _frmCp;
            }
        }
        #endregion

        #region Own Form Events
        void _frmCp_OnPressCancel()
        {
            cts.Cancel();
            StopAllProcess = true;
        }
        void frmConfig_PathChanger(string newPath)
        {
            if (!String.IsNullOrEmpty(newPath))
            {
                lstSchemas.Clear();
                lstItemsChecked.Clear();
                lstItemsBase.Clear();
                tvDirectories.Nodes.Clear();
                DirectoryInfo diNew = new DirectoryInfo(newPath);
                TreeNode trN = new TreeNode("SMGC");
                trN.Tag = diNew;
                tvDirectories.TopNode = trN;
                getFilesFromDI(diNew);
                fillUpDirectories(trN);
            }
            else
            {
                MessageBox.Show(Properties.Settings.Default.NoConfigurationMade,"Attention", MessageBoxButtons.OK);
            }
        }
        #endregion

        #region Constructor
        public frmMainFM()
        {
            InitializeComponent();
            if (CheckInitialValuesNeeded())
            {
                dippal = new DirectoryInfo(Properties.Settings.Default.InitialPath);
                TreeNode trN = new TreeNode("SMGC");
                trN.Tag = dippal;
                tvDirectories.TopNode = trN;
                getFilesFromDI(dippal);
                fillUpDirectories(trN);
                tvDirectories.SelectedNode = tvDirectories.TopNode;
            }
            else
            {
                MessageBox.Show(Properties.Settings.Default.InitialValuesError,"Warning",MessageBoxButtons.OK);
                frmConfiguration frmConfig = new frmConfiguration();
                frmConfig.PathChanger +=new frmConfiguration.OnPathChange(frmConfig_PathChanger);
                frmConfig.Show();
            }
        }

        private bool CheckInitialValuesNeeded()
        {
            bool ans = CommonFunctions.CheckIfConfigurationWasMade();
            return ans;
        }
        #endregion

        #region Private Methods
        /// <summary>
        /// Activates the buttons.
        /// </summary>
        /// <param name="state">if set to <c>true</c> [state].</param>
        private void activateButtons(bool state)
        {
            toolStrip1.Items.OfType<System.Windows.Forms.ToolStripButton>().ToList().ForEach((ctrl) =>
            {
                if (ctrl.Name.StartsWith("tsb"))
                {
                    ctrl.Enabled = state;
                }
            });
        }
        /// <summary>
        /// Activates the buttons.
        /// </summary>
        /// <param name="state">if set to <c>true</c> [state].</param>
        /// <param name="exceptions">The exceptions.</param>
        private void activateButtons(bool state, List<string> exceptions)
        {
            toolStrip1.Items.OfType<System.Windows.Forms.ToolStripButton>().ToList().ForEach((ctrl) =>
            {
                if (ctrl.Name.StartsWith("tsb"))
                {
                    if (ctrl.Name.IsContainedIn<string>(exceptions))
                    {
                        ctrl.Enabled = !state;
                    }
                    else
                    {
                        ctrl.Enabled = state;
                    }
                }
            });
        }
        /// <summary>
        /// Adds the Progress Bar control to form.
        /// </summary>
        /// <param name="ctrl">The CTRL.</param>
        /// <param name="col">The col.</param>
        /// <param name="row">The row.</param>
        private void addPgBControlToForm(Control ctrl, int col, int row)
        {
            if (this.frmCp.InvokeRequired)
            {
                AddControlCallback accb = new AddControlCallback(addPgBControlToForm);
                this.Invoke(accb, new object[] { ctrl, col, row });
            }
            else
            {
                this.frmCp.tlpControls.Controls.Add(ctrl, col, row);
            }
        }
        /// <summary>
        /// Adds the progress bar control.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="curRow">The cur row.</param>
        private void addProgressBarControl(string tableName, int curRow)
        {         
            if (findPbOnForm(tableName) == null) 
            {
                //Progress bar                           
                ProgressBar pb = new ProgressBar();
                pb.Name = "pbd" + tableName;
                pb.Minimum = 0;
                pb.Value = 0;
                pb.Step = 1;                
                //Label
                Label lb = new Label();
                lb.Name = "lbl" + tableName;
                //lb.Text = "Processing " + tableName;
                lb.Text = tableName;
                addPgBControlToForm(lb, 0, curRow);
                addPgBControlToForm(pb, 1, curRow);               
            }           
        }
        /// <summary>
        /// Builds the table schema on database.
        /// </summary>
        /// <param name="dt">The dt.</param>
        /// <param name="lst">The LST.</param>
        private void buildSchema(DataTable dtSchema, DataTable dtData, ListItem lst)
        {
            if (dtSchema != null)
            {
                if (dtData.Rows.Count > 0)
                {
                    dtSchema.Columns.Cast<DataColumn>().ToList().ForEach(delegate(DataColumn dc)
                    {
                        if (!lst.Operations.FieldsAndTypes.Any(k => k.Key.Equals(dc.ColumnName)))
                        {
                            int max = default(int);
                            try
                            {
                                max = dtData.AsEnumerable()
                                            .Select(row => row[dc].ToString().Trim())
                                            .Max(str => str.ToString().Trim().Length);
                            }
                            catch (Exception ex)
                            {
                                SetTraceText(ex.Message);
                            }
                            //the following code assigns ten units more to value for adjusting prouposals
                            max = (max + 10) > 4000 ? max : max + 10;
                            lst.Operations.FieldsAndTypes.Add(dc.ColumnName, CommonFunctions.GetTypeForDB(dc.DataType, max));
                        }
                    });
                }
            }
        }
        private void checkAllItems()
        {
            chLstBArchivos.Items.Cast<ListItem>()
               .Where(l => !l.Checked)
               .ToList()
               .ForEach(delegate(ListItem lst)
               {
                   lst.Checked = true;
                   chLstBArchivos.SetItemChecked(chLstBArchivos.FindStringExact(lst.Text), lst.Checked);
               });
            tsbToDB.Enabled = chLstBArchivos.SelectedIndices.Count > 0;
        }
        private bool CreateTableOnDestiny(ListItem lst)
        {
            bool ans = true;
            string result = string.Empty;
            using (DataFunctions dfcs = new DataFunctions { ConnectionString = CurrentDBCS })
            {
                DataTable dt = dfcs.PrepareToMigrateThis(lst, ref lstSchemas,ref lstData, out result);
                if (dt != null)
                {
                    if (lstData.Any(ky => ky.Key.Equals(lst.Text)))
                    {
                        buildSchema(dt, lstData[lst.Text], lst);
                        string Schema = lst.Operations.GenerateTableSchema(lst.Text);
                        if (Properties.Settings.Default.CurrentDB.Equals("Oracle"))
                        {
                            ans = dfcs.ExecuteNonQueryOracle(ref Schema);
                        }
                        else
                        {
                            ans = dfcs.ExecuteNonQuerySql(ref Schema);
                        }
                    }
                    else
                    {
                        ans = false;
                    }
                }
                else
                {
                    ans = false;
                }
            }
            return ans;
        }
        private string executeParallelTasks()
        {
            string tablesProcessed = string.Empty;
            cts = new CancellationTokenSource();
            ParallelOptions po = new ParallelOptions();
            po.MaxDegreeOfParallelism = (Environment.ProcessorCount / 2) < 1 ? 1 : (Environment.ProcessorCount / 2);
            po.CancellationToken = cts.Token;
            try
            {
                Parallel.ForEach<ListItem>(chLstBArchivos.CheckedItems.Cast<ListItem>()
                .ToList()
                .OrderBy(crt => crt.Operations.Values.Count)
                .AsParallel()
                .WithCancellation(po.CancellationToken), po,
                (lst, ParallelLoopState) =>
                {
                    if (cts.IsCancellationRequested)
                    {
                        ParallelLoopState.Stop();
                        Application.DoEvents();
                    }
                    if (!ParallelLoopState.IsStopped)
                    {
                        if (Convert.ToBoolean(Properties.Settings.Default.CreateSchema))
                        {
                            if (!CreateTableOnDestiny(lst))
                            {
                                SetTraceText(String.Format(Properties.Settings.Default.WarningSchema, lst.Text));
                                if (MessageBox.Show(String.Format(Properties.Settings.Default.EraseOrAppend, lst.Text), "Attention", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
                                {
                                    DeleteAllRowOnThis(lst);
                                }
                            }
                        }
                        if (Convert.ToBoolean(Properties.Settings.Default.TransferData))
                        {
                            deletePreviousPlainText(lst);
                            ProgressBar pb = findPbOnForm(lst.Text);
                            if (pb != null)
                            {
                                MigrateThis(lst, pb);
                            }
                            else
                            {
                                MigrateThis(lst);
                            }
                        }
                        tablesProcessed += lst.Text + ", ";
                        Application.DoEvents();
                    }
                    else
                    {
                        SetTraceText("Migration process stopped!");
                        Application.DoEvents();
                    }
                });
            }
            catch (OperationCanceledException)
            {
                MessageBox.Show(Properties.Settings.Default.OperationCanceled);
            }
            catch (Exception ex) 
            {
                MessageBox.Show(ex.Message);
            }
            return tablesProcessed;
        }
            
        void frmMsg_ReWriteDataAnswer(DialogResult DialogResult, bool Persistance, ListItem CurrentListItem)
        {
            AskAgain = Persistance;
            ReWriteData = DialogResult == System.Windows.Forms.DialogResult.Yes ? true : false;
            DeleteAllRowOnThis(CurrentListItem);            
        }
        private void fillUpDirectories(TreeNode trOld)
        {
            DirectoryInfo di = trOld.Tag as DirectoryInfo;
            foreach (DirectoryInfo dinf in di.GetDirectories())
            {
                TreeNode trNew = new TreeNode(dinf.Name);
                trNew.Name = dinf.Name;
                trNew.Tag = dinf;
                trOld.Nodes.Add(trNew);
                findChildDirectories(trNew);
            }
            tvDirectories.Nodes.Add(trOld);
        }
        private void findChildDirectories(TreeNode Trn)
        {
            DirectoryInfo di = Trn.Tag as DirectoryInfo;
            if (di.GetDirectories().Count() > 0)
            {
                foreach (DirectoryInfo dinf in di.GetDirectories())
                {
                    TreeNode tn = new TreeNode(dinf.Name);
                    tn.Name = dinf.Name;
                    tn.Tag = dinf;
                    Trn.Nodes.Add(tn);
                    findChildDirectories(tn);
                }
            }
        }
        private ProgressBar findPbOnForm(string tableName)
        {
            ProgressBar pb = default(ProgressBar);
            if (this.frmCp.tlpControls.Controls.OfType<ProgressBar>().Any(p => p.Name.Equals("pbd" + tableName)))
            {
                pb = this.frmCp.tlpControls.Controls.OfType<ProgressBar>().Where(p => p.Name.Equals("pbd" + tableName)).SingleOrDefault();
            }
            return pb;
        }
        private List<string> getAllSelectedTables()
        {
            List<string> lstSelVal = new List<string>();
            chLstBArchivos.Items.Cast<ListItem>()
                .Where(l => l.Checked)
                .ToList()
                .ForEach(delegate(ListItem lst)
                {
                    lstSelVal.Add(lst.Text);
                });
            return lstSelVal;
        }
        private string getAllSelectedTablesString()
        {
            string allValues = string.Empty;
            getAllSelectedTables().ForEach(delegate(string str) { allValues += str + Environment.NewLine; });
            return allValues;
        }
        private void getFilesFromDI(DirectoryInfo di)
        {
            if (di != null)
            {
                chLstBArchivos.Items.Clear();
                chLstBArchivos.DisplayMember = "Text";
                foreach (FileInfo fi in di.GetFiles("*.dbf"))
                {
                    ListItem lstitm = new ListItem(fi.Name.Replace(fi.Extension, string.Empty).ToUpper(), fi.Name, fi.FullName, false);
                    if (!chLstBArchivos.Items.Contains(lstitm))
                    {
                        chLstBArchivos.Items.Add(lstitm);
                        //AutoComp.Add(lstitm.Text);
                    }
                }
            }
        }
        private void IncrementPgBProgress(ProgressBar pgb)
        {
            if (pgb.InvokeRequired)
            {
                ProgressCallback accb = new ProgressCallback(IncrementPgBProgress);
                this.Invoke(accb, pgb);
            }
            else
            {
                pgb.PerformStep();
            }
        }
        private void deletePreviousPlainText(ListItem lstItm)
        {
            string filePath = Properties.Settings.Default.TextFilePath + @"\" + lstItm.Text + ".txt";
            if (File.Exists(filePath))
            {
                try
                {
                    File.Delete(filePath);
                }
                catch (IOException ex)
                {
                    MessageBox.Show(String.Format(Properties.Settings.Default.ClearPlainTextError, lstItm.Text + ".txt", ex.Message));
                }
            }
        }
        private void insertIntoPlainText(ListItem lstItm, DataRow dr, string msg)
        {
            List<string> strArray = new List<string>();
            foreach (DataColumn dc in dr.Table.Columns)
            {
                strArray.Add(dr[dc.ColumnName].ToString().Trim());
            }
            strArray.Add("[" + msg + "]");
            string rowForTextFile = string.Join(",", strArray.ToArray());
            string filePath = Properties.Settings.Default.TextFilePath + @"\" + lstItm.Text + ".txt";
            if (!File.Exists(filePath))
            {
                using (StreamWriter fs = File.CreateText(filePath))
                {
                    fs.WriteLine(rowForTextFile + Environment.NewLine);
                    fs.Flush();
                    fs.Close();
                }
            }
            else
            {
                using (StreamWriter fs = File.AppendText(filePath))
                {
                    fs.WriteLine(rowForTextFile);
                    fs.Flush();
                    fs.Close();
                }
            }
        }
        private bool bulkInsertOracle(DataTable dtData, ListItem lst, out string msg)
        {
            List<OracleParameter> prmsOrcl = new List<OracleParameter>();            
            msg = string.Empty;
            bool ans = true;
            List<string> lstCols = new List<string>();
            foreach (DataColumn dc in dtData.Columns)
            {               
                Array arrCol = Array.CreateInstance(dc.DataType, dtData.Rows.Count);
                int[] sizes = new int[dtData.Rows.Count];
                for (int I=0;I<dtData.Rows.Count;I++)
                {
                    sizes.SetValue(dtData.Rows[I][dc.ColumnName].ToString().Trim().Length, I);
                    if (dtData.Rows[I][dc.ColumnName].GetType().Equals(typeof(string)))
                    {                        
                        arrCol.SetValue(dtData.Rows[I][dc.ColumnName].ToString().Trim(),I);
                    }
                    else
                    {
                        arrCol.SetValue(dtData.Rows[I][dc.ColumnName],I);
                    }
                }
                lstCols.Add(":" + dc.ColumnName);
                OracleParameter newOracleParameter = new OracleParameter(dc.ColumnName, CommonFunctions.getOracleType(dc.DataType,sizes.Max()));
                //newOracleParameter.CollectionType = OracleCollectionType.None;
                newOracleParameter.ArrayBindSize = sizes;                
                newOracleParameter.Size = arrCol.Length;
                newOracleParameter.Value = arrCol;
                prmsOrcl.Add(newOracleParameter);
            }

            string strSql = "INSERT INTO " + lst.Text + " VALUES (" + String.Join(",", lstCols) + ")";
            using (DataFunctions dfcs = new DataFunctions { ConnectionString = CurrentDBCS })
            {
                ans = dfcs.ExecuteNonQueryOracle(ref strSql, prmsOrcl, dtData.Rows.Count);
                msg = strSql;
            }
            return ans;           
        }
        
        private bool insertRow(DataRow dr, ListItem lstItm, out string msg)
        {
            msg = string.Empty;
            bool ans = true;
            foreach (KeyValuePair<string, string> kvp in lstItm.Operations.FieldsAndTypes)
            {
                if (dr.Table.Columns.IndexOf(kvp.Key) != -1)
                {
                    if (lstItm.Operations.FieldsAndTypes.Any(k => k.Key.Equals(kvp.Key)))
                    {
                        dynamic value = dr[kvp.Key];
                        if (kvp.Value.ToLower().Contains("varchar"))
                        {
                            string val = Convert.ToString(value).Trim();                            
                            dr.BeginEdit();
                            dr[kvp.Key] = val.Trim();
                            dr.EndEdit();
                        }
                        else if (kvp.Value.ToLower().Contains("clob"))
                        {
                            string val = Convert.ToString(value).Trim();
                            value = "to_clob('" + val + "')";
                            dr.BeginEdit();
                            dr[kvp.Key] = val;
                            dr.EndEdit();
                        }
                        else if (kvp.Value.ToLower().Contains("blob"))
                        {                            
                            Byte[] arrBts = (Byte[])value;
                            //value = "to_blob('" + System.Text.ASCIIEncoding.ASCII.GetString(arrBts) + "')";
                            dr.BeginEdit();
                            dr[kvp.Key] = arrBts;
                            dr.EndEdit();
                        }
                    }
                }
                Application.DoEvents();
            }
            if (Properties.Settings.Default.CurrentDB.Equals("Oracle"))
            {
                List<OracleParameter> prmsOrcl = new List<OracleParameter>();
                string strSql = lstItm.Operations.TranslateToInsertOperationToOracle(lstItm.Text, dr, out prmsOrcl);
                using (DataFunctions dfcs = new DataFunctions { ConnectionString = CurrentDBCS })
                {
                    ans = dfcs.ExecuteNonQueryOracle(ref strSql, prmsOrcl);
                    msg = strSql;
                }
                return ans;
            }
            else
            {
                List<SqlParameter> prms = new List<SqlParameter>();
                string strSql = lstItm.Operations.TranslateToInsertOperationToSql(lstItm.Text, dr, out prms);
                using (DataFunctions dfcs = new DataFunctions { ConnectionString = CurrentDBCS })
                {
                    ans = dfcs.ExecuteNonQuerySql(ref strSql, prms);
                    msg = strSql;
                }
                return ans;
            }            
        }
        private bool DeleteAllRowOnThis(ListItem lst)
        {
            bool blnSucess = false;
            string delSql = String.Format(Properties.Settings.Default.DeleteAllRows, lst.Text);
            using (DataFunctions dfcs = new DataFunctions { ConnectionString = CommonFunctions.GetCurrentCS()})
            {
                if (Properties.Settings.Default.CurrentDB.Equals("Oracle"))
                {
                    blnSucess = dfcs.ExecuteNonQueryOracle(ref delSql);
                }
                else
                {
                    blnSucess = dfcs.ExecuteNonQuerySql(ref delSql);
                }
            }
            return blnSucess;
        }
        private void MigrateThis(ListItem lst)
        {
            int efRecs = 0;
            int dfRecs = 0;
            string strSql = string.Empty;
            string result = string.Empty;
            DataTable dt = new DataTable();
            using (DataFunctions dfcs = new DataFunctions())
            {
                dt = dfcs.PrepareToMigrateThis(lst,ref lstSchemas,ref lstData,out result);
            }
            if (dt != null)
            {
                //dt.TableName = lst.Text;
                //strSql = CommonFunctions.AnalizeColumnsAndTranslateSQL(dt, lst);
                if (dt.Columns.Count > 0)
                {
                    DataTable ftDt = lstData.Where(sch => sch.Key.ToUpper().Equals(lst.Text.ToUpper())).SingleOrDefault().Value;
                    /*string constr = String.Format(Properties.Settings.Default.FoxProCS, lst.FullPath);
                    using (DataFunctions dfcs = new DataFunctions { ConnectionString = constr })
                    {
                        ftDt = dfcs.ExecuteQuery(ref strSql);
                    }*/                    
                    if (ftDt != null && ftDt.Rows.Count > 0)
                    {
                        processRows(lst, ftDt, out efRecs, out dfRecs);
                        SetTraceText("-------------------------------------------------------------------------" + Environment.NewLine);
                        SetTraceText(String.Format(Properties.Settings.Default.ProcessingRecsMsg, lst.Text, efRecs.ToString(), dfRecs.ToString()) + Environment.NewLine);
                        SetTraceText("-------------------------------------------------------------------------" + Environment.NewLine);
                        if (dfRecs > 0)
                        {
                            SetTraceText(String.Format(Properties.Settings.Default.TextFileCreated, Properties.Settings.Default.TextFilePath, lst.Text) + Environment.NewLine);
                        }
                    }
                    else
                    {
                        SetTraceText(String.Format(Properties.Settings.Default.UnexpectedError, lst.Text, strSql) + Environment.NewLine);
                    }
                }
                else
                {
                    SetTraceText(String.Format(Properties.Settings.Default.NoRecordsMsg, lst.Text) + Environment.NewLine);
                }
            }
            else
            {
                SetTraceText(String.Format(Properties.Settings.Default.UnexpectedError, lst.Text, strSql) + Environment.NewLine);
            }
        }
        private void MigrateThis(ListItem lst, ProgressBar pb)
        {
            int efRecs = 0;
            int dfRecs = 0;
            string strSql = string.Empty;
            string strSqlOriginal = string.Empty;
            string result = string.Empty;
            DataTable dt = new DataTable();
            using (DataFunctions dfcs = new DataFunctions())
            {
                dt = dfcs.PrepareToMigrateThis(lst, ref lstSchemas, ref lstData, out result);
            }            
            if (dt != null)
            {
                ProgressBar pgb = findPbOnForm(lst.Text);
                //dt.TableName = lst.Text;
                //strSql = CommonFunctions.AnalizeColumnsAndTranslateSQL(dt, lst);               
                if (dt.Columns.Count > 0)
                {
                    //string constr = String.Format(Properties.Settings.Default.FoxProCS, lst.FullPath);
                    //using (DataFunctions dfcs = new DataFunctions { ConnectionString = constr })
                    //{
                    //    strSqlOriginal = strSql;
                    //    ftDt = dfcs.ExecuteQuery(ref strSql);
                    //    if (ftDt == null && CommonFunctions.CheckIfItsJetEnabled())
                    //    {
                    //        dfcs.ConnectionString = Properties.Settings.Default.JetCS.Replace("{0}", lst.FullPath.ToLower().Replace(lst.TableName.ToLower(), string.Empty));
                    //        strSqlOriginal += ".dbf"; 
                    //        ftDt = dfcs.ExecuteQueryODBC(ref strSqlOriginal);
                    //    }
                    //}
                    DataTable ftDt = lstData.Where(sch => sch.Key.ToUpper().Equals(lst.Text.ToUpper())).SingleOrDefault().Value;
                    if (ftDt != null)
                    {
                        if (ftDt.Rows.Count > 0)
                        {
                            processRows(lst, ftDt, pgb, out efRecs, out dfRecs);
                            SetTraceText("-------------------------------------------------------------------------" + Environment.NewLine);
                            SetTraceText(String.Format(Properties.Settings.Default.ProcessingRecsMsg, lst.Text, efRecs.ToString(), dfRecs.ToString()) + Environment.NewLine);
                            SetTraceText("-------------------------------------------------------------------------" + Environment.NewLine);
                            if (dfRecs > 0)
                            {
                                SetTraceText(String.Format(Properties.Settings.Default.TextFileCreated, Properties.Settings.Default.TextFilePath, lst.Text) + Environment.NewLine);
                            }
                        }
                        else
                        {
                            SetTraceText(String.Format(Properties.Settings.Default.NoRecordsMsg, lst.Text) + Environment.NewLine);
                        }
                    }
                    else
                    {
                        SetTraceText(String.Format(Properties.Settings.Default.UnexpectedError, lst.Text, strSql) + Environment.NewLine);
                    }
                }
                else
                {
                    SetTraceText(String.Format(Properties.Settings.Default.NoRecordsMsg, lst.Text) + Environment.NewLine);
                }
            }
            else
            {
                SetTraceText(String.Format(Properties.Settings.Default.UnexpectedError, lst.Text, strSql) + Environment.NewLine);
            }
        }
        private void prepareProgressBars()
        {
            int counter = 0;
            chLstBArchivos.CheckedItems.Cast<ListItem>().ToList().ForEach(delegate(ListItem lst)
            {
                addProgressBarControl(lst.Text, counter);
                counter++;
            });
        }  
        private void processRows(ListItem lstItm, DataTable fpDt, out int efRecs, out int dfRecs)
        {
            efRecs = 0;
            dfRecs = 0;
            string msg = string.Empty;
            if (Properties.Settings.Default.UseBulkInsert)
            {
                bulkInsertOracle(fpDt, lstItm, out msg);
            }
            else
            {
                //Parallel.ForEach<DataRow>(fpDt.Rows.Cast<DataRow>().ToList(), delegate(DataRow dr) 
                //{
                //    if (insertRow(dr, lstItm, out msg))
                //    {                    
                //        //efRecs++;
                //    }
                //    else
                //    {
                //        insertIntoPlainText(lstItm, dr, msg);
                //        //dfRecs++;
                //    }
                //    this.BeginInvoke((Action)(() => tspbEstado.PerformStep()));
                //    //tspbEstado.PerformStep();       
                //});
                foreach (DataRow dr in fpDt.Rows)
                {
                    if (insertRow(dr, lstItm, out msg))
                    {
                        efRecs++;
                    }
                    else
                    {
                        insertIntoPlainText(lstItm, dr, msg);
                        dfRecs++;
                    }
                }     
            }    
        }
        private void processRows(ListItem lstItm, DataTable fpDt, ProgressBar pgb, out int efRecs, out int dfRecs)
        {
            efRecs = 0;
            dfRecs = 0;
            SetMaximumToProgressBar(pgb, fpDt.Rows.Count);           
            string msg = string.Empty;           
            foreach (DataRow dr in fpDt.Rows)
            {
                if (!cts.IsCancellationRequested)
                {
                    if (Properties.Settings.Default.UseBulkInsert)
                    {
                        bulkInsertOracle(fpDt, lstItm, out msg);
                    }
                    else
                    {
                        if (insertRow(dr, lstItm, out msg))
                        {
                            efRecs++;
                        }
                        else
                        {
                            insertIntoPlainText(lstItm, dr, msg);
                            dfRecs++;
                        }
                    }
                    Application.DoEvents();
                    IncrementPgBProgress(pgb);
                }
                else
                {
                    break;
                }
            }            
        }
        private void SetMaximumToProgressBar(ProgressBar pgb, int maximum)
        {
            if (pgb.InvokeRequired)
            {
                SetMaximumToPb accb = new SetMaximumToPb(SetMaximumToProgressBar);
                this.Invoke(accb, new object[] { pgb, maximum });
            }
            else
            {
                pgb.Maximum = maximum;
            }
        }
        private void SetTraceText(string text)
        {
            if (txtTrace.InvokeRequired)
            {
                SetTextCallback stcb = new SetTextCallback(SetTraceText);
                this.Invoke(stcb, new object[] { text });
            }
            else
            {
                txtTrace.Text += text + Environment.NewLine;
            }
            txtTrace.SelectionStart = txtTrace.Text.Length;
            txtTrace.ScrollToCaret();
            txtTrace.Refresh();
        }
        private void uncheckAllItems()
        {
            chLstBArchivos.CheckedItems.Cast<ListItem>()
             .ToList()
             .ForEach(delegate(ListItem lst)
             {
                 lst.Checked = false;
                 chLstBArchivos.SetItemChecked(chLstBArchivos.FindStringExact(lst.Text), lst.Checked);
             });
            tsbToDB.Enabled = chLstBArchivos.SelectedIndices.Count > 0;
        }
        #endregion

        #region Control Events Methods
        private void tvDirectories_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
        {
            getFilesFromDI(e.Node.Tag as DirectoryInfo);
            tsbCheckAll.Enabled = chLstBArchivos.Items.Count > 0;
            tst.AutoCompleteCustomSource = AutoComp;
        }
        private void chLstBArchivos_ItemCheck(object sender, ItemCheckEventArgs e)
        {
            if (((CheckedListBox)sender).SelectedIndex != -1)
            {
                ((ListItem)chLstBArchivos.Items[((CheckedListBox)sender).SelectedIndex]).Checked = (e.NewValue == CheckState.Checked);
                if (e.NewValue == CheckState.Checked)
                {
                    lstItemsChecked.Add(((ListItem)chLstBArchivos.Items[((CheckedListBox)sender).SelectedIndex]));
                }
                else
                {
                    lstItemsChecked.Remove(((ListItem)chLstBArchivos.Items[((CheckedListBox)sender).SelectedIndex]));
                }
            }
            if (lstItemsChecked.Count > 0)
            {
                List<string> exceptions = new List<string>();
                exceptions.Add(tsbStopAllProcess.Name);
                exceptions.Add(tsbClear.Name);
                activateButtons(true, exceptions);
            }
            else
            {
                List<string> exceptions = new List<string>();
                exceptions.Add(tsbOpen.Name);
                activateButtons(false,exceptions);
            }
        }
        private void tsbToClipBoard_Click(object sender, EventArgs e)
        {
            string values = getAllSelectedTablesString();
            if (!String.IsNullOrEmpty(values))
            {
                Clipboard.Clear();
                Clipboard.SetText(values);
                MessageBox.Show(Properties.Settings.Default.TableNamesToCB);
            }
        }
        private void tsbCheckAll_Click(object sender, EventArgs e)
        {
            checkAllItems();
            List<string> exceptions = new List<string>();
            exceptions.Add(tsbClear.Name);
            activateButtons((lstItemsChecked.Count > 0), exceptions);
        }
        private void tsbUnCheckAll_Click(object sender, EventArgs e)
        {
            uncheckAllItems();
            List<string> exceptions = new List<string>();
            exceptions.Add(tsbClear.Name);
            exceptions.Add(tsbOpen.Name);
            exceptions.Add(tsbClear.Name);
            activateButtons((lstItemsChecked.Count > 0),exceptions);
        }
        private void tsbToDB_Click(object sender, EventArgs e)
        {
            string tablesProcessed = string.Empty;
            string timeStamp = DateTime.Now.ToShortTimeString();
            Stopwatch tmr = new Stopwatch();
            tmr.Start();            
            List<string> exceptions = new List<string>();
            exceptions.Add(tsbStopAllProcess.Name);            
            activateButtons(false, exceptions);
            this.frmCp.tlpControls.Controls.Clear();
            this.frmCp.Show();            
            prepareProgressBars();
            tablesProcessed = executeParallelTasks();            
            #region Secuencial Code
            /*chLstBArchivos.CheckedItems.Cast<ListItem>()
            .ToList()
            .ForEach(delegate(ListItem lst)
            {
                ProgressBar pb = default(ProgressBar);
                if (Convert.ToBoolean(Properties.Settings.Default.CreateSchema))
                {
                    pb = addProgressBarControl(lst.Text, counter);
                    CreateTableOnDestiny(lst);
                }
                if (Convert.ToBoolean(Properties.Settings.Default.TransferData))
                {
                    if (pb != default(ProgressBar))
                    {
                        MigrateThis(lst, pb);
                    }
                    else
                    {
                        MigrateThis(lst);
                    }
                }
                tablesProcessed += lst.Text + ", ";
                counter++;
                Application.DoEvents();
            });*/
            
            #endregion
            tmr.Stop();
            string timeelapsed = ((tmr.ElapsedMilliseconds /1000)/60).ToString() + " minutes";
            SetTraceText(String.Format(Properties.Settings.Default.EndProcessingMsg, tablesProcessed, timeelapsed, timeStamp) + Environment.NewLine);
            if (!cts.IsCancellationRequested)
            {
                MessageBox.Show(Properties.Settings.Default.ProcessFinished);
            }
            activateButtons(false);
            activateButtons(true, exceptions);
            Thread.Sleep(2000);
            if (frmCp.Visible)
            {
                frmCp.Close();
            }
        }                
        private void tsbConfiguration_Click(object sender, EventArgs e)
        {
            frmConfiguration frmConfig = new frmConfiguration();
            frmConfig.PathChanger += new frmConfiguration.OnPathChange(frmConfig_PathChanger);
            frmConfig.Show();
        }        
        private void tst_TextChanged(object sender, EventArgs e)
        {
            if (lstItemsBase.Count == 0)
            {
                lstItemsBase = chLstBArchivos.Items.Cast<ListItem>().ToList();
            }
            if (!String.IsNullOrEmpty(tst.Text))
            {
                List<ListItem> lstFiltered = new List<ListItem>();
                lstItemsBase.Where(l => l.Text.ToLower().StartsWith(tst.Text.ToLower()))
                .ToList()
                .ForEach(delegate(ListItem lst)
                {
                    lstFiltered.Add(lst);
                });
                if (lstFiltered.Count > 0)
                {
                    chLstBArchivos.Items.Clear();
                    lstFiltered.ForEach(delegate(ListItem lst)
                    {
                        chLstBArchivos.Items.Add(lst);
                        if (lstItemsChecked.Any(lt => lt.Text.Equals(lst.Text)))
                        {
                            lst.Checked = true;
                            chLstBArchivos.SetItemChecked(chLstBArchivos.FindStringExact(lst.Text), lst.Checked);
                        }
                    });
                }
            }
            else
            {
                chLstBArchivos.Items.Clear();
                lstItemsBase.ForEach(delegate(ListItem lst)
                {
                    chLstBArchivos.Items.Add(lst);
                    if (lstItemsChecked.Any(lt => lt.Text.Equals(lst.Text)))
                    {
                        lst.Checked = true;
                        chLstBArchivos.SetItemChecked(chLstBArchivos.FindStringExact(lst.Text), lst.Checked);
                    }
                });
            }
        }
        private void tsbStopAllProcess_Click(object sender, EventArgs e)
        {
            try
            {
                if (MessageBox.Show(Properties.Settings.Default.AreYouSureOfCancelMigration, "Caution", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
                {
                    cts.Cancel();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void tsbSave_Click(object sender, EventArgs e)
        {
            bool ansSave = true;
            List<string> strArray = new List<string>();
            strArray.Add("[" + tvDirectories.SelectedNode.FullPath + "]");
            chLstBArchivos.CheckedItems.Cast<ListItem>()
            .ToList()
            .ForEach(delegate(ListItem lst)
            {
                strArray.Add(lst.Text);
            });
            if (strArray.Count > 0)
            {
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Filter = "Migration Files (*.mgf)|*.mgf";
                if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    string rowForTextFile = string.Join(",", strArray.ToArray());
                    try
                    {
                        System.IO.File.WriteAllText(sfd.FileName, rowForTextFile, Encoding.UTF8);
                    }
                    catch (Exception)
                    {
                        ansSave = false;
                    }
                }
                if (ansSave)
                {
                    MessageBox.Show(Properties.Settings.Default.FileSavingSuccess);
                }
            }
            else
            {
                MessageBox.Show(Properties.Settings.Default.ChooseOneTable);
            }
        }
        private void tsbOpen_Click(object sender, EventArgs e)
        {
            List<string> values = new List<string>();
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Migration Files (*.mgf)|*.mgf";
            string currentPath = string.Empty;
            if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                using (StreamReader fs = File.OpenText(ofd.FileName))
                {
                    values = fs.ReadToEnd().Split(new char[] { ',' }).ToList();
                    if (values[0].StartsWith("["))
                    {
                        currentPath = values[0].Replace("[",string.Empty).Replace("]",string.Empty);
                        values.RemoveAt(0);
                        string NodeName = currentPath.Split(new string[] { @"\" }, StringSplitOptions.RemoveEmptyEntries).Last().Replace("]", string.Empty);
                        tvDirectories.Nodes.Find(NodeName, true).ToList()
                        .ForEach(delegate(TreeNode trn)
                        {
                            if (trn.FullPath.Equals(currentPath))
                            {
                                tvDirectories.SelectedNode = trn;
                                getFilesFromDI(trn.Tag as DirectoryInfo); 
                            }
                        });
                    }
                    fs.Close();
                }
            }
            if (values.Count > 0)
            {
                uncheckAllItems();
                chLstBArchivos.Items.Cast<ListItem>()
                .Where(l => !l.Checked && l.Text.IsContainedIn<string>(values))
                .ToList()
                .ForEach(delegate(ListItem lst)
                {
                    lst.Checked = true;
                    chLstBArchivos.SetItemChecked(chLstBArchivos.FindStringExact(lst.Text), lst.Checked);
                });
                List<string> exceptions = new List<string>();
                exceptions.Add(tsbStopAllProcess.Name);                
                activateButtons(true, exceptions);
            }
        }
        private void frmFileLister_HelpRequested(object sender, HelpEventArgs hlpevent)
        {
            abAboutFM abfrm = new abAboutFM();
            abfrm.Show();
        }
        #endregion

        private void tsbClear_Click(object sender, EventArgs e)
        {            
            if (MessageBox.Show(Properties.Settings.Default.ClearLogData,"Attention", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
            {
                txtTrace.Text = string.Empty;
            }
        }

        private void txtTrace_TextChanged(object sender, EventArgs e)
        {
            tsbClear.Enabled = !String.IsNullOrEmpty(txtTrace.Text);
        }

        private void tsbDataPreview_Click(object sender, EventArgs e)
        {            
            chLstBArchivos.CheckedItems.Cast<ListItem>()
            .ToList()
            .ForEach(delegate(ListItem lst)
            {
                frmPreviewData frmPd = new frmPreviewData();
                frmPd.Text = String.Format(frmPd.Text, lst.Text);
                frmPd.getTableData(lst, lstSchemas, lstData);
                frmPd.Show();
            });
        }

        private void tsbDBImportTables_Click(object sender, EventArgs e)
        {
            frmImportTables frmImp = new frmImportTables();
            frmImp.Show();
        }               
    }
}
